package cn.net.lwj.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBTool {

    //private String url = CommonProperties.JDBC_URL;
    //private String user = CommonProperties.JDBC_USER;
    //private String pwd = CommonProperties.JDBC_PWD;

    private String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";
    private String user = "root";
    private String pwd = "passw0rd";

    public DBTool() {
        String driver = "com.mysql.jdbc.Driver";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("找不到这个驱动类", e);
        }
    }

    public DBTool(String url, String user, String pwd) {
        this.url = url;
        this.user = user;
        this.pwd = pwd;
        try {
            //org.gjt.mm.mysql.Driver
            String driver = "com.mysql.jdbc.Driver";
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("找不到这个驱动类", e);
        }
    }

    /**
     * 创建连接
     *
     * @return
     * @throws SQLException
     */
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, pwd);
    }

    /**
     * 关闭连接
     *
     * @param conn 连接
     */
    public void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("关闭连接失败", e);
            }
        }
    }

    /**
     * 开始事务
     * @param conn 连接
     */
    public  static void beginTransaction(Connection conn){
        if (conn != null) {
            try {
                if (conn.getAutoCommit()) {
                    conn.setAutoCommit(false);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 提交事务
     * @param conn 连接
     */
    public static  void commitTransaction(Connection conn){
        if (conn != null) {
            try {
                if (!conn.getAutoCommit()) {
                    conn.commit();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 回滚事务
     * @param conn 连接
     */
    public static void rollBackTransaction(Connection conn){
        if (conn != null) {
            try {
                if (!conn.getAutoCommit()) {
                    conn.rollback();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询封装返回list
     * @param conn
     * @param sql
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> queryForList(Connection conn, String sql) throws Exception {
        //SELECT ID,BT,ZFJBS,SFBLX,XZSL FROM t_xf_yy_dagl_bm_temppz WHERE XTTYPE='XFLX' AND ZFJBS NOT IN (5000,2151,2152) AND bt='来信材料';
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Map<String, Object>> list = new ArrayList<>();
        ResultSetMetaData md = rs.getMetaData();
        int columCount = md.getColumnCount();
        while (rs.next()) {
            Map<String, Object> map = new HashMap<>();
            for (int i = 1; i <= columCount; i++) {
                map.put(md.getColumnName(i).toUpperCase(), rs.getObject(i));//获取键名及值
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 查询封装返回INT  适用于查询数量
     * @param conn
     * @param sql
     * @return
     * @throws Exception
     */
    public static int queryForInt(Connection conn, String sql) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    public static int queryForInt(String sql) throws Exception {
        DBTool dbtool = new DBTool();
        Connection conn = dbtool.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

    public static void execute(Connection conn, String sql) throws Exception {
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.executeUpdate();
        ps.close();
    }

}